using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

public class MySqlDataBase
{
    private string connectString = string.Empty;
    public MySqlDataBase(string ConnectString)
    {
        connectString = ConnectString;
    }
    public DataTable Query(string sql)
    {
        Log.Debug("DataTable::Query() is going to execute sql: " + sql);
        DataTable result = null;

        MySqlConnection conn = null;
        MySqlCommand command = null;
        MySqlDataAdapter da = null;
        try
        {
            conn = new MySqlConnection(connectString);
            command = conn.CreateCommand();
            da = new MySqlDataAdapter();
            conn.Open();
            if (sql.StartsWith("SELECT"))
            {
                da.SelectCommand = command;
                command.CommandText = sql;
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (ds.Tables.Count > 0)
                {
                    DataTable dt = new DataTable();
                    dt = ds.Tables[0];
                    result = dt;
                }
                else
                {
                    result = null;
                }
            }
            else if (sql.StartsWith("INSERT"))
            {
                da.InsertCommand = command;
                command.CommandText = sql;
                command.ExecuteNonQuery();
                result = null;
            }
            else if (sql.StartsWith("UPDATE"))
            {
                da.UpdateCommand = command;
                command.CommandText = sql;
                command.ExecuteNonQuery();
                result = null;
            }
            else if (sql.StartsWith("DELETE"))
            {
                da.DeleteCommand = command;
                command.CommandText = sql;
                command.ExecuteNonQuery();
                result = null;
            }
        }
        catch (MySqlException ex)
        {
            Log.Error("DataTable::Query() exception is caught: " + ex.Message);
            result = null;
        }
        finally
        {
            conn.Close();
            da.Dispose();
            command.Dispose();
            //conn.Dispose();
        }

        return result;
    }
    public int Execute(string sql)
    {
        Log.Debug("DataTable::Query() is going to execute sql: " + sql);
        int result = -1;

        MySqlConnection conn = null;
        MySqlCommand command = null;
        try
        {
            conn = new MySqlConnection(connectString);
            command = conn.CreateCommand();
            conn.Open();
            command.CommandText = sql;
            result = command.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            Log.Error("DataTable::Query() exception is caught: " + ex.Message);
        }
        finally
        {
            conn.Close();
            command.Dispose();
            //conn.Dispose();
        }
        return result;
    }
}
